"""
Case Type   : 功能测试
Case Name   : 使用timestampdiff函数计算两个日期时间之间的差值
Create At   : 2021-01-08
Owner       : xwx973645
Description : TIMESTAMPDIFF(unit , timestamp_expr1, timestamp_expr2)
    timestampdiff函数是计算两个日期时间之间(timestamp_expr2-timestamp_expr1)的差值，并以unit形式返回结果。
    timestamp_expr1，timestamp_expr2必须是一个timestamp、timestamptz、date类型的值表达式。unit表示的是两个日期差的单位。
    该函数仅在openGauss兼容MY类型时（即dbcompatibility = 'B'）有效，其他类型不支持该函数。
    1.创建兼容MY的数据库
    2.在该数据库中执行timestampdiff函数
Expect      :
    1.创建成功
    2.函数返回结果正确
History     :
   modified by wwx1120111 at 2022-10-13:修改预期适配新代码;
   modified by pwx5328113 at 2023-9-28:优化用例预期，研发代码变更，识别了01时区，导致diff结果发生变化;
   modified by pwx5328113 at 2024-3-27:优化用例预期，研发代码变更，识别了不符合格式的日期;
   modified by pwx5328113 at 2024-4-12:优化用例预期，偶现warning信息在返回结果之后，
    导致断言获取信息错误，优化断言信息；整体优化用例
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Function(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.commonsh = CommonSH('dbuser')
        self.db_name = "db_innerfunc_timestampdiff_case0001"
        self.constant = Constant()

    def test_timestampdiff(self):
        sql_cmd = [
            "select timestampdiff(year, '2018-01-01', '2020-01-01');",
            "select timestampdiff(quarter, '2018-01-01', '2020-01-01');",
            "select timestampdiff(month, '2018-01-01', '2020-01-01');",
            "select timestampdiff(week, '2018-01-01', '2020-01-01');",
            "select timestampdiff(day, '2018-01-01', '2020-01-01');",
            "select timestampdiff(hour, '2020-01-01 10:10:10', "
            "'2020-01-01 11:11:11');",
            "select timestampdiff(minute, '2020-01-01 10:10:10', "
            "'2020-01-01 11:11:11');",
            "select timestampdiff(second, '2020-01-01 10:10:10', "
            "'2020-01-01 11:11:11');",
            "select timestampdiff(microsecond, '2020-01-01 10:10:10.000000', "
            "'2020-01-01 10:10:10.111111');",
            "select timestampdiff(hour,'2020-05-01 10:10:10-01',"
            "'2020-05-01 10:10:10-03');"]

        expect = ['2', '8', '24', '104', '730', '1', '61', '3661', '111111',
                  '0']
        warning_msg = 'WARNING:  Truncated incorrect date or datetime value:'
        step_txt = "----step1：建立兼容B库；expect：成功----"
        self.log.info(step_txt)
        db_create = f"drop database if exists {self.db_name};" \
            f"create database {self.db_name} dbcompatibility 'B';"
        create_msg = self.commonsh.execut_db_sql(db_create)
        self.log.info(create_msg)
        self.assertTrue(self.constant.CREATE_DATABASE_SUCCESS in create_msg,
                        '执行失败' + step_txt)

        step_txt = "----step2：执行不同场景timestampdiff语法；expect：正确----"
        self.log.info(step_txt)
        for j in range(len(sql_cmd)):
            result = self.commonsh.execut_db_sql(sql_cmd[j],
                                                 dbname=self.db_name)
            self.log.info(result)
            if j < 9:
                self.assertTrue(result.splitlines()[-2].strip() == expect[j],
                                '执行失败' + step_txt)
            if j == 9:
                self.assertIn(warning_msg, result, '执行失败' + step_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = "----step3：删除库；expect：成功----"
        self.log.info(step_txt)
        db_drop = f"drop database {self.db_name};"
        drop_msg = self.commonsh.execut_db_sql(db_drop)
        self.log.info(drop_msg)
        self.log.info(f'----{os.path.basename(__file__)}:end----')
        self.assertTrue(self.constant.DROP_DATABASE_SUCCESS in drop_msg,
                        '执行失败' + step_txt)
